package com.teacher.hong.dao;

import com.teacher.hong.entity.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 处理学生表数据的类
 */
@Repository
public class StudentDao {

    @Resource
    BaseDao baseDao;

    /**
     * 查询所有班级数据
     *
     * @return
     */
    public List<Student> select(int pageIndex, int pageSize, Integer clazzId, String name) throws SQLException {
        List<Student> students = new ArrayList<>();

        String sql = "select * from student" +
                " left join clazz on student.clazz_id=clazz.id where 1=1";

        List<Object> params = new ArrayList<>();    //参数

        //在这儿添加条件
        if (clazzId > 0) {
            sql += " and clazz_id = ?";
            params.add(clazzId);
        }
        if (name != "") {
            sql += " and student.name like ?";
            params.add("%" + name + "%");
        }
        sql += " order by student.id limit " + (pageIndex - 1) * pageSize + "," + pageSize;

        ResultSet resultSet = baseDao.query(sql, params);
        while (resultSet.next()) {
            int id = resultSet.getInt("student.id");
            String sName = resultSet.getString("student.name");
            int sClazzId = resultSet.getInt("clazz_id");
            String clazzName = resultSet.getString("clazz.name");
            String sex = resultSet.getString("sex");
            short age = resultSet.getShort("age");
            String address = resultSet.getString("address");
            LocalDateTime createtime = LocalDateTime.ofInstant(resultSet.getTimestamp("createtime").toInstant(), ZoneId.systemDefault());
            int money = resultSet.getInt("money");
            Student student = new Student(id, sName, sClazzId, sex, age, address, createtime);
            student.setClazzName(clazzName);
            student.setMoney(money);
            students.add(student);
        }

        return students;
    }

    /**
     * 查询数量
     *
     * @return
     */
    public int selectCount(Integer clazzId, String name) throws SQLException {
        int count = 0;

        String sql = "select count(0) from student where 1=1";
        List<Object> params = new ArrayList<>();    //参数
        //在这儿添加条件
        if (clazzId > 0) {
            sql += " and clazz_id = ?";
            params.add(clazzId);
        }
        if (name != "") {
            sql += " and name like ?";
            params.add("%" + name + "%");
        }
        ResultSet resultSet = baseDao.query(sql, params);
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        return count;
    }

    /**
     * 根据学生ID查询条数
     *
     * @param id
     * @return
     */
    public int selectCountById(int id) throws SQLException {
        int count = 0;

        ResultSet resultSet = baseDao.query("select count(0) from student where id=" + id);
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }

        return count;
    }

    /**
     * 根据班级ID查询学生表条数
     *
     * @param clazzId
     * @return
     */
    public int selectCountByClazzId(int clazzId) throws SQLException {
        int count = 0;
        ResultSet resultSet = baseDao.query("select count(0) from student where clazz_id=" + clazzId);
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        return count;
    }

    /**
     * 插入学生对象到数据库
     *
     * @param student
     * @return
     */
    public int insert(Student student) throws SQLException {
        return baseDao.update("insert into student values(default, ?, ?, ?, ?, ?, now(), ?)",
                Arrays.asList(student.getName(), student.getClazzId(),
                        String.valueOf(student.getSex()), student.getAge(), student.getAddress(), student.getMoney()));
    }

    /**
     * 修改学生对象到数据库
     *
     * @param student
     * @return
     */
    public int update(Student student) throws SQLException {
        return baseDao.update("update student set name=?, clazz_id=?, sex=?, age=?, address=? where id=?",
                    Arrays.asList(student.getName(), student.getClazzId(),
                            String.valueOf(student.getSex()), student.getAge(), student.getAddress(), student.getId()));
    }

    @Resource
    JdbcTemplate jdbcTemplate;

    /**
     * 根据学生ID修改其money
     * @param studentId
     * @param money 需要增减的数值
     * @return
     * @throws SQLException
     */
    public int updateMoney(int studentId, int money) throws SQLException {
        return jdbcTemplate.update("update student set money=money-? where id=?",
                money, studentId);
    }


    /**
     * 根据ID删除学生数据
     *
     * @param id
     * @return
     */
    public int delete(int id) throws SQLException {
        return baseDao.update("delete from student where id=?",
                Arrays.asList(id));
    }

}
